/*
 * Copyright (c) 2006, 2022, webrx.cn All rights reserved.
 *
 */
package cn.webrx.db;

import com.alibaba.druid.pool.DruidDataSource;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Properties;
import java.util.Set;

/**
 * <p>Project: jdbc202202 - DbUtil
 * <p>Powered by webrx On 2022-08-22 15:09:45
 *
 * @author webrx [webrx@126.com]
 * @version 1.0
 * @since 17
 */
public class DbUtil {
    private Connection conn;
    private String driver = "com.mysql.cj.jdbc.Driver";// 数据库驱动类
    private String url = "jdbc:mysql://localhost:3306/db"; //url
    private String user = "root";// 账号
    private String password = "";// 密码
    private DruidDataSource ds;
    private int port = 3306;
    private int currpage = 1;
    private int pagesize = 5;
    private int recordcount = 0;
    private int pagecount = 0;
    private String dbname = "db";

    public DbUtil() {
        //connect();
        ds();
    }

    public String getPk(String tablename) {
        String pk = null;
        DatabaseMetaData dbmd;
        try {
            dbmd = this.conn.getMetaData();
            ResultSet rs = dbmd.getPrimaryKeys(this.dbname, null, tablename);
            if (rs.next()) {
                pk = rs.getString(4);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return pk;
    }

    public int add(String sql, Object[] values) {
        int num = 0;
        PreparedStatement pst;
        try {
            pst = this.conn.prepareStatement(sql);
            int i = 0;
            for (Object o : values) {
                pst.setObject(++i, o);
            }
            num = pst.executeUpdate();
            pst.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return num;
    }

    public int insert(String tablename, Map<String, Object> m) {
        int num = 0;
        StringBuilder n = new StringBuilder();
        StringBuilder v = new StringBuilder();
        for (String k : m.keySet()) {
            v.append("?,");
            n.append(k + ",");
        }
        String sql = String.format("insert into %s(%s) values(%s)", tablename, n.toString().subSequence(0, n.length() - 1), v.toString().subSequence(0, v.length() - 1));
        PreparedStatement pst;
        try {
            pst = this.conn.prepareStatement(sql);
            int i = 0;
            for (Object o : m.values()) {
                pst.setObject(++i, o);
            }
            num = pst.executeUpdate();
            pst.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return num;
    }

    public int deleteById(String tablename, Object id) {
        int num = delete(tablename, this.getPk(tablename) + "=" + id);
        return num;
    }

    public int delete(String tablename, String where) {
        int num = 0;
        String sql = String.format("delete from %s where %s", tablename, where);
        try {
            PreparedStatement pst = this.conn.prepareStatement(sql);
            num = pst.executeUpdate();
            pst.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return num;
    }

    public int delete(String tablename) {
        int num = delete(tablename, "1=1");
        return num;
    }

    public Map<String, Object> queryById(String tablename, Object id) {
        Map<String, Object> m = new HashMap<String, Object>();
        String sql = String.format("select * from %s where %s", tablename, this.getPk(tablename) + "='" + id + "'");
        try {
            PreparedStatement pst = this.conn.prepareStatement(sql);
            ResultSet rs = pst.executeQuery();
            if (rs.next()) {
                ResultSetMetaData rsmd = rs.getMetaData();
                int cc = rsmd.getColumnCount();
                for (int i = 1; i <= cc; i++) {
                    String name = rsmd.getColumnLabel(i);
                    m.put(name, rs.getObject(name));
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return m;
    }

    public int update(String tablename, Map<String, Object> m) {
        int num = 0;
        String pk = this.getPk(tablename);
        if (m.containsKey(pk)) {
            num = update(tablename, m, pk + "='" + m.get(pk) + "'");
        } else {
            num = update(tablename, m, "1=1");
        }
        return num;
    }

    public int update(String tablename, Map<String, Object> m, String where) {
        int num = 0;
        StringBuilder s = new StringBuilder();
        for (String k : m.keySet()) {
            s.append(k + "=?,");
        }
        String sql = String.format("update %s set %s where %s", tablename, s.toString().subSequence(0, s.length() - 1), where);
        PreparedStatement pst;
        try {
            pst = this.conn.prepareStatement(sql);
            int i = 0;
            for (Object o : m.values()) {
                pst.setObject(++i, o);
            }
            num = pst.executeUpdate();
            pst.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return num;
    }

    public void close() {
        if (this.conn != null) {
            try {
                this.conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    public int getCurrpage() {
        return currpage;
    }

    public void setCurrpage(int currpage) {
        this.currpage = currpage;
    }

    public int getPagesize() {
        return pagesize;
    }

    public void setPagesize(int pagesize) {
        this.pagesize = pagesize;
    }

    public int getRecordcount() {
        return recordcount;
    }

    public void setRecordcount(int recordcount) {
        this.recordcount = recordcount;
    }

    public int getPagecount() {
        return pagecount;
    }

    public void setPagecount(int pagecount) {
        this.pagecount = pagecount;
    }


    /* delete from t_bookt */
    public int executeUpdate(String sql, Object... params) {
        int rows = 0;
        try {
            PreparedStatement pst = conn.prepareStatement(sql);
            int index = 1;
            for (Object o : params) {
                pst.setObject(index++, o);
            }
            rows = pst.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return rows;
    }

    public int check(String sql, String... params) {
        int num = 0;
        try {
            PreparedStatement pst = this.conn.prepareStatement(sql);
            int index = 0;
            for (String p : params) {
                pst.setString(++index, p);
            }
            ResultSet rs = pst.executeQuery();
            if (rs.next()) {
                num = rs.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return num;
    }

    public int count(String tablename, String where) {
        int num = 0;
        String sql = String.format("select count(*) from %s where %s", tablename, where);
        try {
            PreparedStatement pst = this.conn.prepareStatement(sql);
            ResultSet rs = pst.executeQuery();
            if (rs.next()) {
                num = rs.getInt(1);
            }
            rs.close();
            pst.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return num;
    }

    public List<Map<String, Object>> query(String tablename, String field) {
        return query(tablename, field, "1=1", "");
    }

    public List<Map<String, Object>> query(String tablename) {
        return query(tablename, "*", "1=1", "");
    }

    public List<Map<String, Object>> query(String tablename, String field, String where, String order) {
        List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
        String sql = String.format("select %s from %s where %s %s", field, tablename, where, order);
        PreparedStatement pst;
        try {
            pst = this.conn.prepareStatement(sql);
            ResultSet rs = pst.executeQuery();
            ResultSetMetaData rsmd = rs.getMetaData();
            while (rs.next()) {
                Map<String, Object> m = new HashMap<String, Object>();
                int cc = rsmd.getColumnCount();
                for (int i = 1; i <= cc; i++) {
                    String name = rsmd.getColumnLabel(i);
                    m.put(name, rs.getObject(name));
                }
                list.add(m);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    public List<Map<String, Object>> page(int currpage, String tablename, String where, String order) {
        return page(currpage, tablename, "*", where, order);
    }

    public List<Map<String, Object>> page(int currpage, String tablename, String order) {
        return page(currpage, tablename, "*", "where 1=1", order);
    }

    public List<Map<String, Object>> page(int currpage, String tablename) {
        return page(currpage, tablename, "*", "where 1=1", "");
    }

    public List<Map<String, Object>> page(int currpage, String tablename, String fields, String where, String order) {
        this.currpage = currpage;
        List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
        String sql = String.format("select %s from %s %s %s limit ?,?", fields, tablename, where, order);
        String qqq = String.format("select count(*) c from %s %s", tablename, where);
        try {
            // 分页信息
            PreparedStatement qpst = this.conn.prepareStatement(qqq);
            ResultSet qrs = qpst.executeQuery();
            if (qrs.next()) {
                this.recordcount = qrs.getInt("c");
                this.pagecount = this.recordcount % this.pagesize == 0 ? this.recordcount / this.pagesize : this.recordcount / this.pagesize + 1;
            }
            if (this.currpage < 1)
                this.currpage = 1;
            if (this.currpage > this.pagecount)
                this.currpage = this.pagecount;

            // 分页结果信息
            PreparedStatement pst = this.conn.prepareStatement(sql);
            pst.setInt(1, this.currpage * this.pagesize - this.pagesize);
            pst.setInt(2, this.pagesize);
            ResultSet rs = pst.executeQuery();
            ResultSetMetaData rsmd = rs.getMetaData();
            while (rs.next()) {
                Map<String, Object> m = new HashMap<String, Object>();
                int cc = rsmd.getColumnCount();
                for (int i = 1; i <= cc; i++) {
                    String name = rsmd.getColumnLabel(i);
                    if (name.equals("create_time")) {
                        LocalDateTime lt = rs.getTimestamp(name).toLocalDateTime();
                        m.put(name, lt.format(DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss")));
                    } else if (name.equals("birthday")) {
                        LocalDate lt = rs.getDate(name).toLocalDate();
                        m.put(name, lt.format(DateTimeFormatter.ofPattern("yyyy年MM月dd日")));
                    } else if(name.equals("sip")) {
                        String ip = rs.getString("sip").equals("0:0:0:0:0:0:0:1") ? "127.0.0.1" : rs.getString("sip");
                        m.put(name, ip);
                    }else{
                        m.put(name, rs.getObject(name));

                    }
                }
                list.add(m);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    public String pagebootstrap() {
        StringBuilder s = new StringBuilder();
        s.append("<ul class=\"pagination pagination-sm justify-content-center\">");
        int start = 1;
        int end = 10;

        if (this.currpage >= 7) {
            start = this.currpage - 5;
            end = this.currpage + 4;
        }
        if (this.currpage != 1) {
            s.append(String.format("<li class=\"page-item\"><a class=\"page-link\" href=\"?p=%d\">上一页</a></li>", this.currpage - 1));
        }
        for (int i = start; i <= end; i++) {
            if (i > this.pagecount)
                break;
            if (this.currpage == i) {
                s.append(String.format("<li class=\"page-item active\"><a class=\"page-link\" href=\"javascript:void(0)\">%d</a></li>", i));
                continue;
            }
            s.append(String.format("<li class=\"page-item\"><a class=\"page-link\" href=\"?p=%d\">%d</a></li>", i, i));
        }
        if (this.currpage < this.pagecount) {
            s.append(String.format("<li class=\"page-item\"><a class=\"page-link\" href=\"?p=%d\">下一页</a></li>", this.currpage + 1));
        }
        s.append("</ul>");
        return s.toString();
    }

    public String pageinfo() {
        StringBuilder s = new StringBuilder();
        s.append("<div class=\"page\">");
        int start = 1;
        int end = 10;

        if (this.currpage >= 7) {
            start = this.currpage - 5;
            end = this.currpage + 4;
        }
        if (this.currpage != 1) {
            s.append(String.format("<a class=\"prev\" href=\"?p=%d\">上一页</a>", this.currpage - 1));
        }
        for (int i = start; i <= end; i++) {
            if (i > this.pagecount)
                break;
            if (this.currpage == i) {
                s.append(String.format("<span>%d</span>", i));
                continue;
            }
            s.append(String.format("<a href=\"?p=%d\">%d</a>", i, i));
        }
        if (this.currpage < this.pagecount) {
            s.append(String.format("<a class=\"next\" href=\"?p=%d\">下一页</a>", this.currpage + 1));
        }
        s.append("</div>");
        return s.toString();
    }


    public List<Map<String, Object>> queryAll(String sql) {
        List<Map<String, Object>> list = new ArrayList<>();
        try {
            PreparedStatement pst = this.conn.prepareStatement(sql);
            ResultSet rs = pst.executeQuery();
            ResultSetMetaData rsmd = rs.getMetaData();
            while (rs.next()) {
                Map<String, Object> map = new HashMap<>();
                for (int i = 1; i <= rsmd.getColumnCount(); i++) {
                    String key = rsmd.getColumnName(i);
                    map.put(key, rs.getObject(i));
                }
                list.add(map);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }


    public String getVersion() {
        String v = "";
        try {
            v = this.conn.getMetaData().getDatabaseProductVersion();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return v;
    }

    public Set<String> dbs() {
        Set<String> set = new HashSet<String>();
        try {
            PreparedStatement pst = conn.prepareStatement("show databases");
            ResultSet rs = pst.executeQuery();
            List<String> sysdb = List.of("information_schema", "performance_schema", "mysql", "sys");
            while (rs.next()) {
                String dn = rs.getString(1);
                if (!sysdb.contains(dn)) {
                    set.add(rs.getString(1));
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return set;
    }

    public Set<String> tbs(String dbname) {
        Set<String> set = new HashSet<String>();
        try {
            PreparedStatement pst = conn.prepareStatement("show tables from  " + dbname);
            ResultSet rs = pst.executeQuery();
            while (rs.next()) {
                set.add(rs.getString(1));
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return set;
    }

    public Set<String> tbs() {
        Set<String> set = new HashSet<String>();
        try {
            PreparedStatement pst = conn.prepareStatement("show tables");
            ResultSet rs = pst.executeQuery();
            while (rs.next()) {
                set.add(rs.getString(1));
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return set;
    }

    public void ds() {
        try {
            var is = Thread.currentThread().getContextClassLoader().getResourceAsStream("db.properties");
            var m = new Properties();
            m.load(is);
            ds = new DruidDataSource();
            ds.setUrl(m.getProperty("db.url"));
            ds.setUsername(m.getProperty("db.username", "root"));
            ds.setPassword(m.getProperty("db.password", ""));
            ds.setDriverClassName(m.getProperty("db.driver", "com.mysql.cj.jdbc.Driver"));
            //ds.setInitialSize(Integer.parseInt(m.getProperty("db.initialize","10")));
            conn = ds.getConnection();
        } catch (Exception e) {
            connect();
        }
    }


    public void connect() {
        try {
            Class.forName(driver);
            this.conn = DriverManager.getConnection(url, user, password);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public void connect(String url, String user, String password) {
        try {
            Class.forName(driver);
            this.conn = DriverManager.getConnection(url, user, password);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public DbUtil(String url, String user, String password) {
        try {
            this.url = url;
            this.user = user;
            this.password = password;
            connect();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public Connection getConn() {
        return conn;
    }

    public void setConn(Connection conn) {
        this.conn = conn;
    }

    public String getDriver() {
        return driver;
    }

    public void setDriver(String driver) {
        this.driver = driver;
    }

    public String getUrl() {
        return url;
    }

    public void setUrl(String url) {
        this.url = url;
    }

    public String getUser() {
        return user;
    }

    public void setUser(String user) {
        this.user = user;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }
}
